/* $RCSFile: RatingDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/AddonNice/PageModules/CrmRating/RatingDB.cs $
 * $log$
 * Revision 19 2010/08/09 09:43:51  christian.surieux
 *   Added Log to cs sources files
 * Revision 6 2010/08/06 19:56:25  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.Diagnostics;


namespace AddonNice.Settings.PageModules.CrmRating
{
	/// <summary>
	/// Class that encapsulates all data logic necessary to add/query/delete
	/// Ratings within the Portal database.
	/// </summary>
    public class RatingDB : DBDataStore
    {

        #region Rating

        public SqlDataReader GetRatingGlobalized(int moduleID) 
        {
            SqlCommand myCommand			=   new SqlCommand("dbo.adn_GetRatingGlobalized", myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

			AddGlobalizeParameters(myCommand);

			SqlDataReader result			=   null;
			try
			{
				myConnection.Open();
				result						=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetRatingGlobalized ex: {0}",exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
        }

        public void DeleteRatingGlobalized(int moduleID) 
        {
            SqlCommand myCommand			= new SqlCommand("dbo.adn_DeleteRatingGlobalized", myConnection);
            myCommand.CommandType			= CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

			AddGlobalizeParameters(myCommand);

            try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB DeleteRatingGlobalized moduleID: {0}, ex: {1}",moduleID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public void UpdateRatingGlobalized(int moduleID, string title,string description,string CrmEntityName,int StarNumber,Guid CrmCampaignID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingGlobalized", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            AddModuleIDParameters(myCommand,moduleID);

			SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 150);
			parameterTitle.Value                =   title;
			myCommand.Parameters.Add(parameterTitle);

            SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
            parameterDescription.Value          =   description;
            myCommand.Parameters.Add(parameterDescription);

			SqlParameter parameterCrmEntityName =   new SqlParameter("@CrmEntityName", SqlDbType.NVarChar, 100);
			parameterCrmEntityName.Value        =   CrmEntityName;
			myCommand.Parameters.Add(parameterCrmEntityName);

			SqlParameter parameterStarNumber    =   new SqlParameter("@StarNumber", SqlDbType.Int);
			parameterStarNumber.Value           =   StarNumber;
			myCommand.Parameters.Add(parameterStarNumber);

			SqlParameter parameterCrmCampaignID =   new SqlParameter("@CrmCampaignID", SqlDbType.UniqueIdentifier);
			parameterCrmCampaignID.Value        =   CrmCampaignID;
			myCommand.Parameters.Add(parameterCrmCampaignID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingGlobalized  moduleID: {0}, ex: {1}",moduleID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public void UpdateRatingCrmID(int moduleID, Guid CrmID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingCrmID", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

			SqlParameter parameterCrmID         =   new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
			parameterCrmID.Value                =   CrmID;
			myCommand.Parameters.Add(parameterCrmID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingCrmID  moduleID: {0}, ex: {1}",moduleID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }

        #endregion Rating

        #region Rating Detail

        /// <summary>
        /// Get all the rating items from a moduel or the portal
        /// </summary>
        public DataTable GetRatingDetailsGlobalized(int moduleID) 
        {
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetRatingDetailsGlobalized", myConnection);
            myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);
			AddGlobalizeParameters(myAdapt.SelectCommand);

			DataTable myDataSet					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataSet);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetRatingDetailsGlobalized ex: {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return CheckDataTableForDates(myDataSet);
        }

        /// <summary>
		/// The GetSingleRatingDetailGlobalized method returns a SqlDataReader containing details
		/// about a specific RatingItem from the Ratings database table.
		/// </summary>
		public SqlDataReader GetSingleRatingDetailGlobalized(int itemID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_GetSingleRatingDetailGlobalized", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            AddItemIDParameters(myCommand,itemID);


			SqlDataReader result			    =   null;
			try
			{
				myConnection.Open();
				result						    =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetSingleRatingDetailGlobalized itemID: {0}, ex: {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
		}
	
		public void DeleteRatingDetailGlobalized(int itemID) 
		{
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteRatingDetailGlobalized", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);


			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB DeleteRatingDetailGlobalized itemID: {0}, ex: {1}",itemID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        /// <summary>
        /// The AddRatingDetail method adds a new Rating item to the
        /// Ratings database table, and returns the ItemID value as a result.
        /// </summary>
        public int AddRatingDetail(int moduleID,string title,string description,Guid CrmEntityID,Guid CrmCampaignID,Guid CrmImageID,int ItemOrder) 
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddRatingDetail", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterItemID		=	new SqlParameter(StStr.ATItemID, SqlDbType.Int, 4);
            parameterItemID.Direction           =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterItemID);

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterTabOrder      =   new SqlParameter("@ItemOrder", SqlDbType.Int);
            parameterTabOrder.Value             =   ItemOrder;
            myCommand.Parameters.Add(parameterTabOrder);

			SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 250);
			parameterTitle.Value                =   title;
			myCommand.Parameters.Add(parameterTitle);

			SqlParameter parameterCrmEntityID   =   new SqlParameter("@CrmEntityID", SqlDbType.UniqueIdentifier);
			parameterCrmEntityID.Value          =   CrmEntityID;
			myCommand.Parameters.Add(parameterCrmEntityID);

			SqlParameter parameterCrmCampaignID =   new SqlParameter("@CrmCampaignID", SqlDbType.UniqueIdentifier);
			parameterCrmCampaignID.Value        =   CrmCampaignID;
			myCommand.Parameters.Add(parameterCrmCampaignID);

            SqlParameter parameterCrmImageID    =   new SqlParameter("@CrmImageID", SqlDbType.UniqueIdentifier);
			parameterCrmImageID.Value           =   CrmImageID;
			myCommand.Parameters.Add(parameterCrmImageID);

            SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
            parameterDescription.Value          =   description;
            myCommand.Parameters.Add(parameterDescription);

			int ret							    =	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret			                    =	(int)parameterItemID.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB AddRatingDetail moduleID: {0}, ex: {1}",moduleID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return ret;
        }

        public void UpdateRatingDetailGlobalized(int itemID,string title,string description,Guid CrmEntityID,Guid CrmCampaignID,Guid CrmImageID,int ItemOrder) 
		{

			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingDetailGlobalized", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            AddItemIDParameters(myCommand,itemID);

            SqlParameter parameterTabOrder      =   new SqlParameter("@ItemOrder", SqlDbType.Int);
            parameterTabOrder.Value             =   ItemOrder;
            myCommand.Parameters.Add(parameterTabOrder);

			SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 250);
			parameterTitle.Value                =   title;
			myCommand.Parameters.Add(parameterTitle);

			SqlParameter parameterCrmEntityID   =   new SqlParameter("@CrmEntityID", SqlDbType.UniqueIdentifier);
			parameterCrmEntityID.Value          =   CrmEntityID;
			myCommand.Parameters.Add(parameterCrmEntityID);

			SqlParameter parameterCrmCampaignID =   new SqlParameter("@CrmCampaignID", SqlDbType.UniqueIdentifier);
			parameterCrmCampaignID.Value        =   CrmCampaignID;
			myCommand.Parameters.Add(parameterCrmCampaignID);

            SqlParameter parameterCrmImageID    =   new SqlParameter("@CrmImageID", SqlDbType.UniqueIdentifier);
			parameterCrmImageID.Value           =   CrmImageID;
			myCommand.Parameters.Add(parameterCrmImageID);

			SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
			parameterDescription.Value          =   description;
			myCommand.Parameters.Add(parameterDescription);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateTabGlobalized itemID: {0}, ex: {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public void UpdateRatingDetailCrmID(int itemID, Guid CrmID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingDetailCrmID", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			SqlParameter parameterCrmID         =   new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
			parameterCrmID.Value                =   CrmID;
			myCommand.Parameters.Add(parameterCrmID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingDetailCrmID itemID: {0}, ex: {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        /// <summary>
		/// Only updates the Item Order
		/// </summary>
        public void UpdateRatingDetailOrder(int moduleID, int itemID,int ItemOrder) 
		{

			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingDetailOrder", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterTabOrder      =   new SqlParameter("@ItemOrder", SqlDbType.Int);
            parameterTabOrder.Value             =   ItemOrder;
            myCommand.Parameters.Add(parameterTabOrder);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingDetailOrder itemID: {0}, ex: {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }

        #endregion Rating Detail

        #region Rating Answer

        /// <summary>
        /// The AddRatingDetail method adds a new Rating item to the
        /// Ratings database table, and returns the ItemID value as a result.
        /// </summary>
        public int AddRatingAnswer(int RatingItemID,decimal RatingValue,string IPAddress,
                string UserName,
                Guid UserId,
                string EMail,
	            string Phone,
	            string Address,
                string City,
                string Zip,
                string Country,
                string Comments
            )
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddRatingAnswer", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterItemID		=	new SqlParameter(StStr.ATItemID, SqlDbType.Int, 4);
            parameterItemID.Direction           =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterItemID);


            SqlParameter parameterRatingItemID  =   new SqlParameter("@RatingItemID", SqlDbType.Int);
            parameterRatingItemID.Value         =   RatingItemID;
            myCommand.Parameters.Add(parameterRatingItemID);
            
            SqlParameter parameterRatingValue   =   new SqlParameter("@RatingValue", SqlDbType.Float);
            parameterRatingValue.Value          =   (double)RatingValue;
            myCommand.Parameters.Add(parameterRatingValue);

            SqlParameter parameterIPAddress     =   new SqlParameter("@IPAddress", SqlDbType.NVarChar, 200);
			parameterIPAddress.Value            =   IPAddress;
			myCommand.Parameters.Add(parameterIPAddress);

            SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
			parameterUserName.Value             =   UserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterUserId        =   new SqlParameter("@UserId", SqlDbType.UniqueIdentifier);
			parameterUserId.Value               =   UserId;
			myCommand.Parameters.Add(parameterUserId);

            SqlParameter parameterEMail         =   new SqlParameter("@EMail", SqlDbType.NVarChar,100);
            parameterEMail.Value                =   EMail;
            myCommand.Parameters.Add(parameterEMail);

            SqlParameter parameterPhone         =   new SqlParameter("@Phone", SqlDbType.NVarChar,100);
            parameterPhone.Value                =   Phone;
            myCommand.Parameters.Add(parameterPhone);

            SqlParameter parameterAddress       =   new SqlParameter("@Address", SqlDbType.NVarChar,250);
            parameterAddress.Value              =   Address;
            myCommand.Parameters.Add(parameterAddress);

            SqlParameter parameterCity          =   new SqlParameter("@City", SqlDbType.NVarChar,100);
            parameterCity.Value                 =   City;
            myCommand.Parameters.Add(parameterCity);

            SqlParameter parameterZip           =   new SqlParameter("@Zip", SqlDbType.NVarChar,50);
            parameterZip.Value                  =   Zip;
            myCommand.Parameters.Add(parameterZip);

            SqlParameter parameterCountry       =   new SqlParameter("@Country", SqlDbType.NVarChar,100);
            parameterCountry.Value              =   Country;
            myCommand.Parameters.Add(parameterCountry);

            SqlParameter parameterComments      =   new SqlParameter("@Comments", SqlDbType.NVarChar,1000);
            parameterComments.Value             =   Comments;
            myCommand.Parameters.Add(parameterComments);

            int ret							    =	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret			                    =	(int)parameterItemID.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB AddRatingAnswer RatingItemID: {0}, ex: {0}",RatingItemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return ret;
        }

        /// <summary>
        /// Get all the rating answers from a module or the portal
        /// </summary>
        public DataTable GetRatingAnswersByModule(int moduleID) 
        {
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetRatingAnswersByModule", myConnection);
            myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);

			DataTable myDataSet					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataSet);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetRatingAnswersByModule ex: {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return CheckDataTableForDates(myDataSet);
        }

        /// <summary>
        /// Get all the rating answers from a module or the portal
        /// </summary>
        public DataTable GetRatingAnswersByRatingItem(int RatingItemID) 
        {
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetRatingAnswersByRatingItem", myConnection);
            myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

            SqlParameter parameterRatingItemID  =   new SqlParameter("@RatingItemID", SqlDbType.Int);
            parameterRatingItemID.Value         =   RatingItemID;
            myAdapt.SelectCommand.Parameters.Add(parameterRatingItemID);

			DataTable myDataTb					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetRatingAnswersByRatingItem ex: {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return CheckDataTableForDates(myDataTb);
        }

        /// <summary>
        /// The AddRatingDetail method adds a new Rating item to the
        /// Ratings database table, and returns the ItemID value as a result.
        /// </summary>
        public void UpdateRatingAnswer(int ItemID,int RatingItemID,decimal RatingValue,string IPAddress,
                string UserName,
                Guid UserId,
                string EMail,
	            string Phone,
	            string Address,
                string City,
                string Zip,
                string Country,
                string Comments
            )
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_UpdateRatingAnswer", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterItemID		=	new SqlParameter(StStr.ATItemID, SqlDbType.Int, 4);
            parameterItemID.Value               =   ItemID;
            myCommand.Parameters.Add(parameterItemID);


            SqlParameter parameterRatingItemID  =   new SqlParameter("@RatingItemID", SqlDbType.Int);
            parameterRatingItemID.Value         =   RatingItemID;
            myCommand.Parameters.Add(parameterRatingItemID);

            SqlParameter parameterRatingValue   =   new SqlParameter("@RatingValue", SqlDbType.Float);
            parameterRatingValue.Value          =   (double)RatingValue;
            myCommand.Parameters.Add(parameterRatingValue);

            SqlParameter parameterIPAddress     =   new SqlParameter("@IPAddress", SqlDbType.NVarChar, 200);
			parameterIPAddress.Value            =   IPAddress;
			myCommand.Parameters.Add(parameterIPAddress);

            SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
			parameterUserName.Value             =   UserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterUserId        =   new SqlParameter("@UserId", SqlDbType.UniqueIdentifier);
			parameterUserId.Value               =   UserId;
			myCommand.Parameters.Add(parameterUserId);

            SqlParameter parameterEMail         =   new SqlParameter("@EMail", SqlDbType.NVarChar,100);
            parameterEMail.Value                =   EMail;
            myCommand.Parameters.Add(parameterEMail);

            SqlParameter parameterPhone         =   new SqlParameter("@Phone", SqlDbType.NVarChar,100);
            parameterPhone.Value                =   Phone;
            myCommand.Parameters.Add(parameterPhone);

            SqlParameter parameterAddress       =   new SqlParameter("@Address", SqlDbType.NVarChar,250);
            parameterAddress.Value              =   Address;
            myCommand.Parameters.Add(parameterAddress);

            SqlParameter parameterCity          =   new SqlParameter("@City", SqlDbType.NVarChar,100);
            parameterCity.Value                 =   City;
            myCommand.Parameters.Add(parameterCity);

            SqlParameter parameterZip           =   new SqlParameter("@Zip", SqlDbType.NVarChar,50);
            parameterZip.Value                  =   Zip;
            myCommand.Parameters.Add(parameterZip);

            SqlParameter parameterCountry       =   new SqlParameter("@Country", SqlDbType.NVarChar,100);
            parameterCountry.Value              =   Country;
            myCommand.Parameters.Add(parameterCountry);

            SqlParameter parameterComments      =   new SqlParameter("@Comments", SqlDbType.NVarChar,1000);
            parameterComments.Value             =   Comments;
            myCommand.Parameters.Add(parameterComments);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingAnswer ItemID: {0}, ex: {0}",ItemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }

		public void UpdateRatingAnswerCrmID(int itemID, Guid CrmID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateRatingAnswerCrmID", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			SqlParameter parameterCrmID         =   new SqlParameter("@CrmID", SqlDbType.UniqueIdentifier);
			parameterCrmID.Value                =   CrmID;
			myCommand.Parameters.Add(parameterCrmID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB UpdateRatingAnswerCrmID itemID: {0}, ex: {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }

        #endregion Rating Answer

        #region Utilities

        /// <summary>
        /// Returns the average rating for an item
        /// </summary>
        public decimal GetAverageRating(int RatingItemID,ref int TimesRated)
        {
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GetAverageRating", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;


            SqlParameter parameterRatingItemID  =   new SqlParameter("@RatingItemID", SqlDbType.Int);
            parameterRatingItemID.Value         =   RatingItemID;
            myCommand.Parameters.Add(parameterRatingItemID);

            SqlParameter parameterAverageRating =   new SqlParameter("@AverageRating", SqlDbType.Float);
            parameterAverageRating.Direction    =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterAverageRating);
            
            SqlParameter parameterTimesRated   =   new SqlParameter("@TimesRated", SqlDbType.Int);
            parameterTimesRated.Direction      =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterTimesRated);

            decimal ret						    =	0m;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret			                    =	(decimal)parameterAverageRating.Value;
                TimesRated                      =   (int)parameterTimesRated.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]RatingDB GetAverageRating RatingItemID: {0}, ex: {0}",RatingItemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return ret;
        }

        #endregion Utilities
    }
}

